﻿using QWManage.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace QWManage.DAL
{
    public class BlockedDAL : SqlDAL
    {
        public BlockedDAL()
        {
            TableName = "QW_Blocked";
        }
        //按客户ID和分享人UID做为一个黑名单标识

        public void Add(IEnumerable<BlockedInfo> blockedUsers)
        {
            foreach (BlockedInfo user in blockedUsers)
            {
                var obj = GetBlockedInfo(user.SID, user.UID);
                if (obj != null)
                {
                    user.ID = obj.ID;
                    Update(user);
                }
                else Insert(user);
            }
        }

        public List<string> GetSNames()
        {
            List<string> list = new List<string>();

            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select SName from {TableName} group by SName";
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(reader.GetString(0));
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        public HashSet<string> GetAvatarHashSet()
        {
            HashSet<string> set = new HashSet<string>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = "select Avatar from " + TableName;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string avatar = reader.GetString(0);
                        if (string.IsNullOrEmpty(avatar)) continue;
                        set.Add(avatar);
                    }
                }
                return set;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<BlockedInfo> GetBlockedUserInfos()
        {
            List<BlockedInfo> list = new List<BlockedInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = "select * from " + TableName;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        BlockedInfo userInfo = GetBlockedUserInfo(reader);
                        list.Add(userInfo);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        public BlockedInfo GetUserInfoByID(int id)
        {
            BlockedInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where ID=@ID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetBlockedUserInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }

        public BlockedInfo GetBlockedInfo(string sid, string uid)
        {
            BlockedInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where SID=@SID and UID=@UID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@SID", sid));
                cmd.Parameters.Add(new SqlParameter("@UID", uid));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetBlockedUserInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool Insert(BlockedInfo obj)
        {
            string sql = $"insert into {TableName} (SID,SName,STime,UID,Name,Avatar) values (@SID,@SName,@STime,@UID,@Name,@Avatar)";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(GetStringSqlParameter("SID", obj.SID));
                cmd.Parameters.Add(GetStringSqlParameter("SName", obj.SName));
                cmd.Parameters.Add(new SqlParameter("@STime", obj.STime));
                cmd.Parameters.Add(GetStringSqlParameter("UID", obj.UID));
                cmd.Parameters.Add(GetStringSqlParameter("Name", obj.Name));
                cmd.Parameters.Add(GetStringSqlParameter("Avatar", obj.Avatar));
                conn.Open();
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool Delete(int id)
        {
            string sql = "delete from  " + TableName + " where ID=@ID";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                conn.Open();
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool Update(BlockedInfo obj)
        {
            string sql = $"update {TableName} set SID=@SID,SName=@SName,STime=@STime,UID=@UID,Name=@Name,Avatar=@Avatar where ID=@ID";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(GetStringSqlParameter("SID", obj.SID));
                cmd.Parameters.Add(GetStringSqlParameter("SName", obj.SName));
                cmd.Parameters.Add(new SqlParameter("STime", obj.STime));
                cmd.Parameters.Add(GetStringSqlParameter("UID", obj.UID));
                cmd.Parameters.Add(GetStringSqlParameter("Name", obj.Name));
                cmd.Parameters.Add(GetStringSqlParameter("Avatar", obj.Avatar));
                cmd.Parameters.Add(new SqlParameter("@ID", obj.ID));
                conn.Open();
                return cmd.ExecuteNonQuery() == 1;
            }
            finally
            {
                conn.Close();
            }
        }

        public BlockedInfo GetBlockedUserInfoByUID(string uid)
        {
            BlockedInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where UID=@UID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@UID", uid));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetBlockedUserInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }

        public BlockedInfo GetBlockedUserInfoByName(string name)
        {
            BlockedInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where Name=@Name";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetBlockedUserInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }

        public BlockedInfo GetBlockedUserInfoByAvatar(string avatar)
        {
            BlockedInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where Avatar=@Avatar";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@Avatar", avatar));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetBlockedUserInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }

        private BlockedInfo GetBlockedUserInfo(SqlDataReader reader)
        {
            BlockedInfo obj = new BlockedInfo();
            obj.ID = (int)reader["ID"];
            obj.SID = ReadString(reader, "SID");
            obj.SName = ReadString(reader, "SName");

            if (reader["STime"] != DBNull.Value)
                obj.STime = (DateTime)reader["STime"];

            obj.UID = ReadString(reader, "UID");
            obj.Name = ReadString(reader, "Name");
            obj.Avatar = ReadString(reader, "Avatar");
            return obj;
        }
    }
}
